# coding:utf8
"""
get_mysql_data.py
"""
import sys
import os
f_path = os.path.abspath(os.path.join(__file__, "../.."))
sys.path.append(f_path)

from mylib.funcs import get_conn, select_data, exec_sql, unicode_2_utf8_new, data_types_2_str
import time
import datetime
import json
from collections import OrderedDict
DBS = {
	'local': {
		'MYSQL_HOST': '127.0.0.1',
		'MYSQL_DBNAME': 'spider',
		'MYSQL_USER': 'root',
		'MYSQL_PASSWD': 'root'
	}
}
SELECT_OPTIONS = OrderedDict(
	[('line_item_num', u'在线商品总数统计'),
	('line_item_num2', u'在线商品总数统计2'),
	('line_item_num3', u'在线商品总数统计3'),
	('line_item_num4', u'在线商品总数统计4'),
	('line_item_num5', u'在线商品总数统计5'),
	('line_item_num6', u'在线商品总数统计6'),]
)

def get_c_type_list(conn, start=None, end=None):
	"""
	获取客户种类
	:param conn: 数据库连接
	:param start: 开始时间
	:param end: 结束时间
	:return: 客户列表
	"""
	items = []
	sql = "select c_type, count(1) from item_num_stati where " \
	"add_date >='%s' and add_date <= '%s' group by c_type " % (start, end)

	data = select_data(conn, 'xxx', sql)
	for d in data:
		d = list(d)
		items.append(unicode_2_utf8_new(d[0]))

	return items


def get_c_type_item_info(conn, c_type, start=None, end=None):
	"""
	根据客户获取相关数据
	:param c_type: 客户类型
	:param start: 开始时间
	:param end: 结束时间
	:param conn: 数据库连接
	"""
	sql = "SELECT item_num, add_time, c_name FROM item_num_stati WHERE " \
	"add_date >= '%s' AND add_date <= '%s' AND c_type = '%s';" % (start, end, c_type)
	datas = data_types_2_str(select_data(conn, 'xxx', sql))
	nums = []
	add_times = []
	for data in datas:
		if data[0] == 'get count faild!':
			data_0 = 0
		else:
			data_0 = int(data[0])
		nums.append(data_0)
		add_times.append(data[1])
		c_name = data[2]

	return [c_type, nums, add_times, c_name]


def get_line_item_num(start=None, end=None):
	"""
	根据客户列表获取相关数据
	:param conn: 数据库连接
	:param start: 开始时间
	:param end: 结束时间
	"""
	if not start and not end:
		start = time.strftime("%Y-%m-%d", time.localtime(int(time.time()) - 1 * 24 * 3600))
		end =  time.strftime("%Y-%m-%d")
	elif not end:
		end = time.strftime("%Y-%m-%d")
	elif not start:
		start = time.strftime("%Y-%m-%d")

	conn = get_conn(DBS, 'local')
	c_type_list = get_c_type_list(conn, start, end)
	series = []
	item_len = 0
	i = 0
	for c_type in c_type_list:
		i += 1
		item = get_c_type_item_info(conn, c_type, start, end)
		data = {
			'name': item[3],
			'data': item[1],
		}
		series.append(data)
		if item_len < len(item[2]): # 取最多个数
			item_len = len(item[2])
			addtimes = item[2]
	items_c = [addtimes, series]	
	conn.close()

	return json.dumps(items_c)


# print get_line_item_num()

"""
End get_mysql_data.py
"""